package com.dao.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dao.NoteDAO;
import com.dbc.ConnectionManager;
import com.vo.Message;
import com.vo.Revert;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class NoteDAOImpl implements NoteDAO {
    @Override
    public JSONArray getMessageByPage(int page, int limit) {
        JSONArray jsonArray = new JSONArray();
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from message order by writeDate desc limit ?,?";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, page * limit);
            preparedStatement.setInt(2, limit);
            resultSet = preparedStatement.executeQuery();
            JSONObject jsonObj = null;
            while (resultSet.next()) {
                jsonObj = new JSONObject();
                jsonObj.put("messageID", resultSet.getInt(1));
                jsonObj.put("title", resultSet.getString(2));
                jsonObj.put("content", resultSet.getString(3));
                jsonObj.put("writer", resultSet.getString(4));
                jsonObj.put("writerDate", resultSet.getTimestamp(5).toString());
                jsonObj.put("count", resultSet.getInt(6));
                jsonArray.add(jsonObj);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeResultSet(resultSet);
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return jsonArray;
    }

    @Override
    public JSONArray getInfoByKeyWord(String keyWord) {
        JSONArray jsonArray = new JSONArray();
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select m.*,ifnull(r.revertID, -1),r.content,r.writer,r.writeDate from message m left join revert r on m.messageID=r.messageID where m.content like ? or r.content like ?";
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, "%" + keyWord + "%");
            preparedStatement.setString(2, "%" + keyWord + "%");
            resultSet = preparedStatement.executeQuery();

            JSONObject jsonObj1 = null;
            JSONObject jsonObj2 = null;
            int messageID;
            int revertID;
            int flag;
            while (resultSet.next()) {
                flag = 0;
                messageID = resultSet.getInt(1);
                for (Object object : jsonArray) {
                    jsonObj1 = (JSONObject) object;
                    if ((int) jsonObj1.get("messageID") == messageID) {
                        flag = 1;
                        break;
                    }
                }

                if (flag == 0) {
                    jsonObj1 = new JSONObject();
                    jsonObj1.put("messageID", messageID);
                    jsonObj1.put("title", resultSet.getString(2));
                    jsonObj1.put("content", resultSet.getString(3));
                    jsonObj1.put("writer", resultSet.getString(4));
                    jsonObj1.put("writerDate", resultSet.getTimestamp(5).toString());
                    jsonObj1.put("count", resultSet.getInt(6));
                    jsonObj1.put("list", new JSONArray());
                    jsonArray.add(jsonObj1);
                }
                revertID = resultSet.getInt(7);
                if (revertID != -1) {
                    jsonObj2 = new JSONObject();
                    jsonObj2.put("messageID", messageID);
                    jsonObj2.put("revertID", resultSet.getInt(7));
                    jsonObj2.put("content", resultSet.getString(8));
                    jsonObj2.put("writer", resultSet.getString(9));
                    jsonObj2.put("writerDate", resultSet.getTimestamp(10).toString());
                    ((JSONArray) (jsonObj1.get("list"))).add(jsonObj2);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeResultSet(resultSet);
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return jsonArray;
    }

    @Override
    public JSONArray getRevertByMessageID(int messageId) {
        JSONArray jsonArray = new JSONArray();
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from revert where messageID=? order by writeDate desc";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, messageId);
            resultSet = preparedStatement.executeQuery();
            JSONObject jsonObj = new JSONObject();
            while (resultSet.next()) {
                jsonObj = new JSONObject();
                jsonObj.put("revertID", resultSet.getInt(1));
                jsonObj.put("messageID", resultSet.getInt(2));
                jsonObj.put("content", resultSet.getString(3));
                jsonObj.put("writer", resultSet.getString(4));
                jsonObj.put("writerDate", resultSet.getTimestamp(5).toString());
                jsonArray.add(jsonObj);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeResultSet(resultSet);
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return jsonArray;
    }

    @Override
    public String getRevertWriterByID(int revertId) {
        String writer = null;
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select writer from revert where revertID=?";
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, revertId);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                writer = resultSet.getString(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeResultSet(resultSet);
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return writer;
    }

    @Override
    public String getMessageWriterByID(int messageId) {
        String writer = null;
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select writer from message where messageID=?";
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, messageId);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                writer = resultSet.getString(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeResultSet(resultSet);
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return writer;
    }

    @Override
    public boolean updateMessage(Message message) {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "update message set title=?,content=?,writer=?,writeDate=? where messageID=?";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, message.getTitle());
            preparedStatement.setString(2, message.getContent());
            preparedStatement.setString(3, message.getWriter());
            preparedStatement.setTimestamp(4, message.getWriteDate());
            preparedStatement.setInt(5, message.getMessageID());
            if (preparedStatement.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return false;
    }

    @Override
    public boolean addMessage(Message message) {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "insert into message(title, content, writer, writeDate, count) VALUES (?,?,?,?,?)";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, message.getTitle());
            preparedStatement.setString(2, message.getContent());
            preparedStatement.setString(3, message.getWriter());
            preparedStatement.setTimestamp(4, message.getWriteDate());
            preparedStatement.setInt(5, message.getCount());
            if (preparedStatement.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return false;
    }

    @Override
    public boolean deleteMessage(int messageId) {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        String sql1 = "delete from revert where messageID=?";
        String sql2 = "delete from message where messageID=?";

        try {
            preparedStatement = conn.prepareStatement(sql1);
            preparedStatement.setInt(1, messageId);
            preparedStatement.executeUpdate();

            preparedStatement = conn.prepareStatement(sql2);
            preparedStatement.setInt(1, messageId);
            if (preparedStatement.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return false;
    }

    @Override
    public boolean updateRevert(Revert revert) {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "update revert set messageID=?,content=?,writer=?,writeDate=? where revertID=?";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, revert.getMessageID());
            preparedStatement.setString(2, revert.getContent());
            preparedStatement.setString(3, revert.getWriter());
            preparedStatement.setTimestamp(4, revert.getWriteDate());
            preparedStatement.setInt(5, revert.getRevertID());
            if (preparedStatement.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return false;
    }

    @Override
    public boolean addRevert(Revert revert) {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "insert into revert(messageID, content, writer, writeDate) VALUES (?,?,?,?)";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, revert.getMessageID());
            preparedStatement.setString(2, revert.getContent());
            preparedStatement.setString(3, revert.getWriter());
            preparedStatement.setTimestamp(4, revert.getWriteDate());
            if (preparedStatement.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return false;
    }

    @Override
    public boolean deleteRevert(int revertId) {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "delete from revert where revertID=?";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, revertId);

            if (preparedStatement.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionManager.closeStatement(preparedStatement);
            ConnectionManager.closeConnection(conn);
        }
        return false;
    }

}
